﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
    public class ScratchUtility : BaseUtility
    {
        public ScratchUtility(DataManager manager)
            : base(manager)
        { }

        /// <summary>
        /// Insert into ScratchCall log Table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>


        public ReturnMessage<ScratchPadViewModel> InsertToCallLog(ScratchPadViewModel model)
        {
            var returnMessage = new ReturnMessage<ScratchPadViewModel>();
           
            ScratchPadViewModel sc = new ScratchPadViewModel();
            if (model.updateDate.CompareTo(DateTime.MinValue) == 0)
                model.updateDate = DateTime.Now;

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.note))
                model.note = "";
            if (String.IsNullOrEmpty(model.inOrOut))
                model.inOrOut = "";

            if (String.IsNullOrEmpty(model.loggedOwner))
                model.loggedOwner = "";

            if (sc.visibleToOthers != null)
            {
                model.visibleToOthers = true;
            }

            if (!model.visibleToOthers.HasValue) //check for a value
            {
                // Assume that IsInitialized
                // returns either true or false.
                model.visibleToOthers = false;
            }



            var id = this.InsertToCallLog(model.userId, model.loggedOwner, model.caller, model.phone, model.ContactId, model.companyid, model.productid, DateTime.Now, model.viaId, model.note, model.inOrOut, model.status,
                (bool)model.visibleToOthers);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                //Insert Records to ccCommunication table
                string Feedback = "";
                if (!string.IsNullOrEmpty(model.note))
                {
                    this.InsertScratchFeedBack(model.loggedOwner, model.note, model.id);
                    Feedback += " " + model.loggedOwner + " : " + model.note + " <br/> ";
                }
                int CommunicationID = this.Bridge.CommunicationUtility.Insert(model.userId, model.loggedOwner,model.updateDate,model.inOrOut, Feedback, model.companyid, model.ContactId, id, model.viaId, "", model.updateDate, 0, model.productid, 0, model.note, model.updateDate, model.status, model.phone, "");
                if (!string.IsNullOrEmpty(model.note))
                {
                    this.InsertCommunicationFeedBack(model.loggedOwner, model.note, CommunicationID);
                }
                returnMessage.Status = true;
                //
                #region insert current  user as assigined employee by default


                DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userId);
                int empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                this.Bridge.CommEmployeeUtility.Insert(id, empIdByDefault, DateTime.Now);

                #endregion

                if (model.ContactId != 73)
                {
                    try
                    {
                        this.Bridge.CommunicationContacts.Insert(CommunicationID, model.ContactId, DateTime.Now);


                        this.Bridge.CompanyContactsUtility.Insert(CommunicationID, model.ContactId);
                    }
                    catch
                    { }
                    //------Insert into communication employee when we set up the current user id employeeid  -------//
                }

                if (model.companyid != 36 && model.ContactId != 73)
                {
                    try
                    {
                        this.Bridge.CompanyContactsUtility.Insert(CommunicationID, model.ContactId);
                    }
                    catch
                    { }
                }
                else
                {
                    returnMessage.Status = false;
                    returnMessage.Message = "Record  can not be inserted to database";
                }

            }
            return returnMessage;
        }

     /// <summary>
     /// Update ScratchCall Log Table 
     /// </summary>
     /// <param name="model"></param>
     /// <returns></returns>

        public ReturnMessage<ScratchPadViewModel> UpdateCallLog(ScratchPadViewModel model)
        {
            var returnMessage = new ReturnMessage<ScratchPadViewModel>();
            //if (this.ValidateScratchPadModel(model))
            //{
            ScratchPadViewModel sc = new ScratchPadViewModel();
            if (model.updateDate.CompareTo(DateTime.MinValue) == 0)
                model.updateDate = DateTime.Now;

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.note))
                model.note = "";
            if (String.IsNullOrEmpty(model.inOrOut))
                model.inOrOut = "";

            if (String.IsNullOrEmpty(model.loggedOwner))
                model.loggedOwner = "";

            if (sc.visibleToOthers != null)
            {
                model.visibleToOthers = true;
            }


            if (!model.visibleToOthers.HasValue) //check for a value
            {
                // Assume that IsInitialized
                // returns either true or false.
                model.visibleToOthers = false;
            }

            var id = this.UpdateToCallLog(model.clid, model.userId, model.loggedOwner, model.caller, model.phone, model.ContactId, model.companyid, model.productid, model.viaId, model.note, model.inOrOut, model.status,
                (bool)model.visibleToOthers, DateTime.Now);
            // var id = this.UpdateToCallLog(model.userId, model.caller, model.phone, model.ContactId, model.companyid, model.productid, model.via, model.notes, model.inOrOut, model.statusId, model.visibleToOthers, model.updateDate);
            if (id == -1)
            {
                returnMessage.Message = "We cannot update data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                if (!string.IsNullOrEmpty(model.note))
                    this.InsertScratchFeedBack(model.loggedOwner, model.note, model.id);
                string feedback = this.getfeedbackbyCommunicationId(model.id);
                if (!string.IsNullOrEmpty(model.note))
                {
                    feedback += " " + model.note + " <br/> ";
                }
                this.Bridge.CommunicationUtility.Update(model.id, model.userId, model.loggedOwner,model.updateDate,model.inOrOut,DateTime.Now, feedback, model.companyid, model.ContactId, model.viaId, "", 0, model.productid, model.note, DateTime.Now, model.status);
                int CommunicationID = this.Bridge.CommunicationUtility.GetCommunicationIdByScratchCallLogId(model.id);
                if (!string.IsNullOrEmpty(model.note))
                {
                    this.InsertCommunicationFeedBack(model.loggedOwner, model.note, CommunicationID);
                }

                DataRow dr = this.Bridge.CommunicationUtility.GetTheId(id);
                //int commId = model.companyid;

                //if (commId == 0)
                int commId = Convert.ToInt32(dr["id"].ToString());

                #region insert current  user as assigined employee by default


                DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userId);
                int empIdByDefault = Convert.ToInt32(empDr["EmployeeId"]);
                this.Bridge.CommEmployeeUtility.Insert(commId, empIdByDefault, DateTime.Now);
                #endregion
                if (model.ContactId != 0)
                {
                    try
                    {
                        this.Bridge.CommunicationContacts.Delete(commId);
                        this.Bridge.CommunicationContacts.Insert(commId, model.ContactId, DateTime.Now);
                    }
                    catch
                    { }
                }
                if (model.companyid != 36 && model.ContactId != 0)
                {
                    try
                    {
                        this.Bridge.CompanyContactsUtility.Delete(commId);
                        this.Bridge.CompanyContactsUtility.Insert(model.companyid, model.ContactId);
                    }
                    catch
                    { }
                }

                returnMessage.Status = true;
            }
            return returnMessage;

        }



        /// <summary>
        /// Insert Back to back communications between logged in users
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnMessage<FeedbackVM> InsertFeedback(FeedbackVM model)
        {
            var returnMessage = new ReturnMessage<FeedbackVM>();
            var id = this.InsertScratchFeedBack(model.CreatedUser, model.note, model.scratchpadId);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.Id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }


        /// <summary>
        /// Get Feedback communication by Id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string getfeedbackbyCommunicationId(int id)
        {

            string result = "";
            string sql = string.Format("select isnull(feedback,' ') from ccCommunications where  commId =  " + id);
            try
            {
                result = this.Factory.ExecuteScalar(sql).ToString();
            }
            catch { }
            return result;
          
        }
        public bool ValidateScratchPadModel(ScratchPadViewModel model)
        {
            //Add validation
            Regex regPhone = new Regex(@"^[2-9]\d{2}-\d{3}-\d{4}$");

            if (!regPhone.IsMatch(model.phone))
                return false;

            return true;
        }
      
        /// <summary>
        /// Get All ScratchCall history
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetHistory(int userId)
        {

            string sql = " SELECT cl.*, ' ' as note , cl.id as clid,cl.statusId as status,isnull(cm.description,'') as commVia, " +
                 "isnull((select cc.lastName+','  +  cc.firstName from ccContacts as cc where cc.id = cl.ContactId), '') as contactname, " +
                                     " cs.Id,isnull(cs.firstname+' '+cs.lastname,'') as Name, s.description as statusName, " +
                                     " isnull(c.name,'None') as company, ISNULL(p.product,'None') as product, cl.loggedOwner , " +
                                "   (SELECT replace(REPLACE(replace(isnull(STUFF((SELECT top(5) ' ' + SN.CreatedUser + ' : ' + SN.note + ' <br/> ' FROM tbl_scratchpad_Note as SN " +

                           "  where SN.Scratchpadid = cl.id order by sn.id desc FOR XML PATH('')), 1, 1, ''), ''), '&lt;', '<'), '&', '>'),'gt;','')) as feedback " +


                                     " FROM ScratchCallLogs cl  " +
                                     " LEFT JOIN ddlCommunicationMedium cm ON  cm.id = cl.via " +
                                     " LEFT JOIN ccContacts cs ON cs.Id = cl.ContactId " +
                                     " LEFT JOIN ContactScratch ch ON ch.id = cl.ContactId" +
                                     " LEFT JOIN ddlProducts p ON p.id = cl.productId" +
                                     " LEFT JOIN ddlcompany c ON c.id = cl.companyId" +
                                     " LEFT JOIN ddlStatus s ON s.id = cl.statusId" +
                                     " WHERE s.id <> 1 AND cl.userId = " + userId + " OR cl.visibleToOthers = 'true' " + " ORDER BY cl.date DESC";
            return this.Factory.ExecuteTable(sql);

        }
        /// <summary>
        /// Get All ScratchCall log data
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<ScratchPadViewModel> GetHistoryList(int userId)
        {
            var dt = this.GetHistory(userId);

            return CommonUtility.ToListNew<ScratchPadViewModel>(dt);
        }
      
      /// <summary>
      /// Insert Communications in Scrapad feed back table
      /// </summary>
      /// <param name="createduser"></param>
      /// <param name="notes"></param>
      /// <param name="scratchpadid"></param>
      /// <returns></returns>
        public int InsertScratchFeedBack(string createduser, string notes, int scratchpadid)
        {
            int nRtn = -1;
            string sql = "INSERT INTO tbl_ScratchPad_Note " +
                " (CreatedUser, note,scratchpadId) " +
                " VALUES (@CreatedUser, @note, @scratchpadId) " +
                " SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 50, createduser);
                this.Factory.AddParameter(cmd, "@note", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@scratchpadId", SqlDbType.Int, 10, scratchpadid);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn; //retruning the data stored in the table
        }



        /// <summary>
        /// Insert Communications in Scrapad feed back table
        /// </summary>
        /// <param name="createduser"></param>
        /// <param name="notes"></param>
        /// <param name="scratchpadid"></param>
        /// <returns></returns>
        public int InsertCommunicationFeedBack(string CreatedUser, string note, int CommId)
        {
            int nRtn = -1;
            string sql = "INSERT INTO tbl_Communication_feedback " +
                " (CreatedUser, note,CommId) " +
                " VALUES (@CreatedUser, @note, @CommId) " +
                " SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 50, CreatedUser);
                this.Factory.AddParameter(cmd, "@note", SqlDbType.NVarChar, 1000, note);
                this.Factory.AddParameter(cmd, "@CommId", SqlDbType.Int, 10, CommId);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn; //retruning the data stored in the table
        }
        /// <summary>
        /// Insert Into ScracthPad table
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="loggedOwner"></param>
        /// <param name="caller"></param>
        /// <param name="phone"></param>
        /// <param name="contactId"></param>
        /// <param name="companyId"></param>
        /// <param name="productId"></param>
        /// <param name="date"></param>
        /// <param name="via"></param>
        /// <param name="notes"></param>
        /// <param name="inOrOut"></param>
        /// <param name="statusId"></param>
        /// <param name="visibleToOthers"></param>
        /// <returns></returns>
        public int InsertToCallLog(int userId, string loggedOwner, string caller, string phone, int contactId, int companyId, int productId, DateTime date, int via, string notes, string inOrOut, int statusId, bool visibleToOthers)
        {
            int nRtn = -1;
            string sql = "INSERT INTO ScratchCallLogs " +
                 " ( userId,loggedOwner,caller,phone,contactId, companyId,productId, date, via, notes, inOrOut,statusId,visibleToOthers,updateDate ) " +
                 " VALUES (@userId, @loggedOwner,@caller,@phone,@contactId, @companyId,@productId,@date, @via, @notes, @inOrOut,@statusId,@visibleToOthers,@updateDate) " +
                 " SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@loggedOwner", SqlDbType.NVarChar, 50, loggedOwner);
                this.Factory.AddParameter(cmd, "@caller", SqlDbType.NVarChar, 100, caller);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 100, phone);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.DateTime, 0, date);
                this.Factory.AddParameter(cmd, "@via", SqlDbType.Int, 10, via);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@inOrOut", SqlDbType.NVarChar, 7, inOrOut);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 10, productId);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Bit, 0, visibleToOthers);
                this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 0, date);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table
        }

        public int UpdateToCallLog(int id, int userId, string loggedOwner, string caller, string phone, int contactId, int companyId, int productId, int via, string notes, string inOrOut, int statusId, bool visibleToOthers, DateTime updateDate)
        {
            int nRtn = -1;
            string sql = "UPDATE ScratchCallLogs " +
                 " SET userId=@userId, loggedOwner = @loggedOwner,caller=@caller,phone=@phone,contactId=@contactId, companyId=@companyId," +
                 " productId=@productId, via=@via, notes=@notes, inOrOut=@inOrOut, statusId=@statusId," +
                 "visibleToOthers=@visibleToOthers,updateDate=@updateDate " +
                 " WHERE id =@id ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@loggedOwner", SqlDbType.NVarChar, 50, loggedOwner);
                this.Factory.AddParameter(cmd, "@caller", SqlDbType.NVarChar, 100, caller);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 100, phone);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@via", SqlDbType.Int, 10, via);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@inOrOut", SqlDbType.NVarChar, 7, inOrOut);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 10, productId);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Bit, 0, visibleToOthers);
                this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 0, updateDate);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }

            //cmdUpdate.Parameters.Add("@Id", SqlDbType.Int, 10);
            //cmdUpdate.Parameters["@id"].Value = id;
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }
    
        public int Insert(int contactId, int ComMed, DateTime callDate, int status, string notes, bool m2Cont)
        {
            int nRtn = -1;
            string sql = "INSERT INTO ContactScratch " +
                " (contactId, CommunicationMedium, " +
                " CallDate, Status, Notes, m2c) " +
                " VALUES (@contactId, @CommunicationMedium, @CallDate, @Status, @Notes, @m2c) " +
                " SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@CommunicationMedium", SqlDbType.Int, 10, ComMed);
                this.Factory.AddParameter(cmd, "@CallDate", SqlDbType.SmallDateTime, 0, callDate);
                this.Factory.AddParameter(cmd, "@Status", SqlDbType.Int, 10, status);
                this.Factory.AddParameter(cmd, "@m2c", SqlDbType.Bit, 0, m2Cont);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                // this.Factory.ExecuteNonQuery(cmd);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;
                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }
       
        public void Delete(int id)
        {
            string sql = " DELETE FROM ScratchCallLogs WHERE ID = @ID ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }
        }
       
        public List<CommunicationViewModel> GetPhoneNumbers()
        {
            string sql = "SELECT phone from ScratchCallLogs";
            return this.Factory.ExecuteObjects<CommunicationViewModel>(sql);
        }
  
        public int Insert(int userId, int companyId, int contactId, int receiverId, int projectId, int communicationId, int solutionId, DateTime callDate, DateTime callStartTime, DateTime callEndTime, string subjectDes, string callStatus, DateTime followupDate, bool serviceCall, string priority, bool billable, string notes, bool visibleToOther)
        {
            int nRtn = -1;
            string sql = "INSERT INTO ccCallsLog" +
                  "(userId, companyId, contactId, receiverID, projectId, communicationId, solutionId, callDate, callStartTime, callEndTime, subjectDes, callStatus, followupDate, serviceCall, priority, billable, notes, visibleToOther)" +
              "VALUES(@userId, @companyId, @contactId, @receiverID, @projectId, @communicationId, @solutionId, @callDate, @callStartTime, @callEndTime, @subjectDes, @callStatus, @followupDate, @serviceCall, @priority, @billable, @notes, @visibleToOther)" +
              "SELECT @ID =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@receiverId", SqlDbType.Int, 10, receiverId);
                this.Factory.AddParameter(cmd, "@projectId", SqlDbType.Int, 10, projectId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@communicationId", SqlDbType.Int, 10, communicationId);
                this.Factory.AddParameter(cmd, "@solutionId", SqlDbType.Int, 10, solutionId);
                this.Factory.AddParameter(cmd, "@callDate", SqlDbType.SmallDateTime, 0, callDate);
                this.Factory.AddParameter(cmd, "@callStartTime", SqlDbType.SmallDateTime, 0, callStartTime);
                this.Factory.AddParameter(cmd, "@callEndTime", SqlDbType.SmallDateTime, 0, callEndTime);
                this.Factory.AddParameter(cmd, "@visibleToOthers", SqlDbType.Bit, 0, visibleToOther);
                this.Factory.AddParameter(cmd, "@followupDate", SqlDbType.SmallDateTime, 0, followupDate);
                this.Factory.AddParameter(cmd, "@serviceCall", SqlDbType.Bit, 10, serviceCall);
                this.Factory.AddParameter(cmd, "@priority", SqlDbType.NVarChar, 10, priority);
                this.Factory.AddParameter(cmd, "@billable", SqlDbType.Bit, 10, billable);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.VarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@subjectDes", SqlDbType.NVarChar, 1000, subjectDes);
                this.Factory.AddParameter(cmd, "@callStatus", SqlDbType.VarChar, 1000, callStatus);
                // this.Factory.AddParameter(cmd, "@Id", SqlDbType.Int, 10,id);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.SqlValue);

            }
            return nRtn; //retruning the data stored in the table
        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM scratchPad ";
            this.Factory.ExecuteTable(sql);
        }
    }
}

